package cn.com.cvit.nc.poi;

import java.beans.IntrospectionException;
import java.lang.reflect.InvocationTargetException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import cn.com.cvit.nc.bean.Subscribe;
import cn.com.cvit.nc.bean.SubscribeCount;
import cn.com.cvit.nc.bean.SubscribeCountParameter;
import cn.com.cvit.nc.dao.SubscribeCountAnalysisMapper;
import cn.com.cvit.nc.service.SubscribeCountAnalysisService;
import cn.com.cvit.nc.service.SubscribeCountService;

@Service
public class ExcelService {
	@Autowired
	SubscribeCountService subscribeCountService;
	@Autowired
	SubscribeCountAnalysisService subscribeCountAnalysisService;
	//所有设备类别使用时长
	public XSSFWorkbook exportExcelInfo() throws InvocationTargetException, ClassNotFoundException, IntrospectionException, ParseException, IllegalAccessException {  
	    //根据条件查询数据，把数据装载到一个list中  
	    List<SubscribeCount> list = subscribeCountService.selectDeviceCategoryAllUseTime();  
	    List<SubscribeCount> lists = new ArrayList<SubscribeCount>();
	    List<ExcelBean> excel=new ArrayList<>();  
	    Map<Integer,List<ExcelBean>> map=new LinkedHashMap<>();  
	    for(int i = 0; i < list.size(); i++){
	    	lists.add(list.get(i));
	    }
	    XSSFWorkbook xssfWorkbook=null;  
	    //设置标题栏  
	    excel.add(new ExcelBean("类别ID","deviceCategoryId",0));
	    excel.add(new ExcelBean("设备类别","deviceCategoryName",0));  
	    excel.add(new ExcelBean("使用时长","deviceCategoryAllUseTime",0));  
	    map.put(0, excel);  
	    String sheetName = "所有设备类别使用时长";  
	    //调用ExcelUtil的方法  
	    xssfWorkbook = ExcelUtil.createExcelFile(SubscribeCount.class, lists, map, sheetName);  
	    return xssfWorkbook;  
	}  
	//单一设备使用时长
	public XSSFWorkbook excelEveryDeviceTimesAndNumber(SubscribeCountParameter parameter) throws Exception{
		//根据条件查询数据，把数据装载到一个list中  
	    List<SubscribeCount> list = subscribeCountAnalysisService.selectEveryDeviceTimes(parameter);
	    List<SubscribeCount> lists = new ArrayList<SubscribeCount>();
	    List<ExcelBean> excel=new ArrayList<>();  
	    Map<Integer,List<ExcelBean>> map=new LinkedHashMap<>();  
	    for(int i = 0; i < list.size(); i++){
	    	lists.add(list.get(i));
	    }
	    XSSFWorkbook xssfWorkbook=null;  
	    //设置标题栏  
	    excel.add(new ExcelBean("设备Id","deviceId",0));
	    excel.add(new ExcelBean("类别名称","deviceCategoryName",0));
	    excel.add(new ExcelBean("设备名称","deviceName",0));
	    excel.add(new ExcelBean("使用总时长","allDeviceAllUseTime",0));
	    excel.add(new ExcelBean("使用次数","useNumber",0));
	    
	    map.put(0, excel);  
	    String sheetName = "设备使用时长及次数";  
	    //调用ExcelUtil的方法  
	    xssfWorkbook = ExcelUtil.createExcelFile(SubscribeCount.class, lists, map, sheetName);  
	    return xssfWorkbook;
	}
	//单一设备类别使用时长
	public XSSFWorkbook excelEveryDeviceCategoryTimesAndNumber(SubscribeCountParameter parameter) throws Exception{
		//根据条件查询数据，把数据装载到一个list中  
	    List<SubscribeCount> list = subscribeCountAnalysisService.selectEveryDeviceCategoryTimes(parameter);
	    List<SubscribeCount> lists = new ArrayList<SubscribeCount>();
	    List<ExcelBean> excel=new ArrayList<>();  
	    Map<Integer,List<ExcelBean>> map=new LinkedHashMap<>();  
	    for(int i = 0; i < list.size(); i++){
	    	lists.add(list.get(i));
	    }
	    XSSFWorkbook xssfWorkbook=null;  
	    //设置标题栏  
	    excel.add(new ExcelBean("设备类别Id","deviceCategoryId",0));
	    excel.add(new ExcelBean("设备类别名称","deviceCategoryName",0));
	    excel.add(new ExcelBean("使用总时长","allDeviceAllUseTime",0));
	    excel.add(new ExcelBean("使用次数","useNumber",0));
	    
	    map.put(0, excel);  
	    String sheetName = "设备类别使用时长及次数";  
	    //调用ExcelUtil的方法  
	    xssfWorkbook = ExcelUtil.createExcelFile(SubscribeCount.class, lists, map, sheetName);  
	    return xssfWorkbook;
	}
	//单一用户使用时长及次数
	public XSSFWorkbook excelEveryUserTimesAndNumdber(SubscribeCountParameter parameter) throws Exception{
		//根据条件查询数据，把数据装载到一个list中  
	    List<SubscribeCount> list = subscribeCountAnalysisService.selectEveryUserTimes(parameter);
	    List<SubscribeCount> lists = new ArrayList<SubscribeCount>();
	    List<ExcelBean> excel=new ArrayList<>();  
	    Map<Integer,List<ExcelBean>> map=new LinkedHashMap<>();  
	    for(int i = 0; i < list.size(); i++){
	    	lists.add(list.get(i));
	    }
	    XSSFWorkbook xssfWorkbook=null;  
	    //设置标题栏  
	    excel.add(new ExcelBean("用户Id","personId",0));
	    excel.add(new ExcelBean("用户Email","personEmail",0));
	    excel.add(new ExcelBean("用户名","personName",0));
	    excel.add(new ExcelBean("部门名称","organizeName",0));
	    excel.add(new ExcelBean("使用总时长","allDeviceAllUseTime",0));
	    excel.add(new ExcelBean("使用次数","useNumber",0));
	    
	    map.put(0, excel);  
	    String sheetName = "用户使用时长及次数";  
	    //调用ExcelUtil的方法  
	    xssfWorkbook = ExcelUtil.createExcelFile(SubscribeCount.class, lists, map, sheetName);  
	    return xssfWorkbook;
	}
	//单一部门使用时长和次数
	public XSSFWorkbook excelEveryOrganizeTimesAndNumber(SubscribeCountParameter parameter) throws Exception{
		//根据条件查询数据，把数据装载到一个list中  
	    List<SubscribeCount> list = subscribeCountAnalysisService.selectEveryOrganizeTimes(parameter);
	    List<SubscribeCount> lists = new ArrayList<SubscribeCount>();
	    List<ExcelBean> excel=new ArrayList<>();  
	    Map<Integer,List<ExcelBean>> map=new LinkedHashMap<>();  
	    for(int i = 0; i < list.size(); i++){
	    	lists.add(list.get(i));
	    }
	    XSSFWorkbook xssfWorkbook=null;  
	    //设置标题栏  
	    excel.add(new ExcelBean("部门ID","organizeId",0));
	    excel.add(new ExcelBean("部门名称","organizeName",0));
	    excel.add(new ExcelBean("使用总时长","allDeviceAllUseTime",0));
	    excel.add(new ExcelBean("使用次数","useNumber",0));
	    
	    map.put(0, excel);  
	    String sheetName = "部门使用时长和次数";  
	    //调用ExcelUtil的方法  
	    xssfWorkbook = ExcelUtil.createExcelFile(SubscribeCount.class, lists, map, sheetName);  
	    return xssfWorkbook;
	}
	//预约数据统计
	public XSSFWorkbook excelSubscribeCountData(SubscribeCountParameter parameter) throws Exception{
		//根据条件查询数据，把数据装载到一个list中  
	    List<Subscribe> list = subscribeCountAnalysisService.selectSubscribeCountData(parameter);
	    //List<Subscribe> listToString = list;
	    List<Subscribe> lists = new ArrayList<Subscribe>();
	    List<ExcelBean> excel=new ArrayList<>();  
	    Map<Integer,List<ExcelBean>> map=new LinkedHashMap<>(); 
	    SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	    
	    for(int i = 0; i < list.size(); i++){
	    	lists.add(list.get(i));
	    }
	    XSSFWorkbook xssfWorkbook=null;  
	    //设置标题栏  
	    //excel.add(new ExcelBean("预约ID","id",0));
	    //excel.add(new ExcelBean("用户ID","subscribeUserId",0));
	    excel.add(new ExcelBean("用户名称","subscribeUserName",0));
	    excel.add(new ExcelBean("用户Email","subscribeUserEmail",0));
	    excel.add(new ExcelBean("用户电话","subscribeUserMobile",0));
	    excel.add(new ExcelBean("部门名称","subscribeOrganizeName",0));
	    //excel.add(new ExcelBean("设备ID","subscribeDeviceId",0));
	    excel.add(new ExcelBean("预约开始时间","subscribeBeginTime",0));
	    excel.add(new ExcelBean("预约结束时间","subscribeEndTime",0));
	    excel.add(new ExcelBean("实际开始时间","subscribeBeginTimes",0));
	    excel.add(new ExcelBean("实际结束时间","subscribeEndTimes",0));
	    excel.add(new ExcelBean("预约创建时间","subscribeCreatetime",0));
	    excel.add(new ExcelBean("预约终止时间","subscribeBackouttime",0));
	    excel.add(new ExcelBean("备注","subscribeRemark",0));
	    //excel.add(new ExcelBean("预约状态","subscribeStatus",0));
	    excel.add(new ExcelBean("预约状态","subscribeStatusName",0));
	    excel.add(new ExcelBean("撤销原因","subscribeResult",0));
	    //excel.add(new ExcelBean("状态","flag",0));
	    excel.add(new ExcelBean("状态","flagName",0));
	    //excel.add(new ExcelBean("name","name",0));
	    excel.add(new ExcelBean("设备编号","deviceNumber",0));
	    excel.add(new ExcelBean("品牌","deviceBrand",0));
	    excel.add(new ExcelBean("型号名称","deviceType",0));
	    //excel.add(new ExcelBean("类别","deviceClass",0));
	    excel.add(new ExcelBean("位置描述","devicePlace",0));
	    excel.add(new ExcelBean("设备状态","deviceStatus",0));
	    //excel.add(new ExcelBean("部门名称","organizeName",0));
	    map.put(0, excel);  
	    String sheetName = "预约数据统计";  
	    //调用ExcelUtil的方法  
	    xssfWorkbook = ExcelUtil.createExcelFile(Subscribe.class, lists, map, sheetName);  
	    return xssfWorkbook;
	}

}

/*
		excel.add(new ExcelBean("设备使用总时长","allDeviceAllUseTime",0));
	    excel.add(new ExcelBean("设备类别名称","deviceCategoryName",0));
	    excel.add(new ExcelBean("设备名称","deviceName",0));
	    excel.add(new ExcelBean("单一类别使用总时长","deviceCategoryAllUseTime",0));
	    excel.add(new ExcelBean("使用次数","useNumber",0));
	    excel.add(new ExcelBean("用户名","personName",0));
	    excel.add(new ExcelBean("设备Id","deviceId",0));
	    excel.add(new ExcelBean("设备类别Id","deviceCategoryId",0));
	    excel.add(new ExcelBean("用户Id","personId",0));
	    excel.add(new ExcelBean("用户Email","personEmail",0));
	    excel.add(new ExcelBean("部门名称","organizeName",0));
	    excel.add(new ExcelBean("部门ID","organizeId",0));
	    excel.add(new ExcelBean("部门时间","organizeNameTime",0));
 */
